# Excel转CSV PowerShell脚本
# 将Excel文件的所有sheet分别保存为CSV文件

param(
    [string]$ExcelFile = "波影高压发生器灯丝测试报告20251020(2).xlsx"
)

# 检查文件是否存在
if (-not (Test-Path $ExcelFile)) {
    Write-Host "错误: 找不到文件 '$ExcelFile'" -ForegroundColor Red
    Write-Host "请确保文件在当前目录中" -ForegroundColor Red
    exit 1
}

try {
    # 创建Excel应用程序对象
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    
    # 打开Excel文件
    $workbook = $excel.Workbooks.Open((Resolve-Path $ExcelFile).Path)
    
    Write-Host "正在处理文件: $ExcelFile" -ForegroundColor Green
    Write-Host "发现 $($workbook.Sheets.Count) 个sheet:" -ForegroundColor Yellow
    
    # 获取文件名（不含扩展名）
    $baseName = [System.IO.Path]::GetFileNameWithoutExtension($ExcelFile)
    
    # 遍历所有sheet
    for ($i = 1; $i -le $workbook.Sheets.Count; $i++) {
        $sheet = $workbook.Sheets.Item($i)
        $sheetName = $sheet.Name
        
        Write-Host "  $i. $sheetName" -ForegroundColor Cyan
        
        try {
            # 清理sheet名称中的特殊字符
            $safeSheetName = $sheetName -replace '[^\w\s-]', '' -replace '\s+', '_'
            $csvFileName = "${baseName}_${safeSheetName}.csv"
            
            # 选择当前sheet
            $sheet.Select()
            
            # 保存为CSV文件
            $csvPath = Join-Path (Get-Location) $csvFileName
            $workbook.SaveAs($csvPath, 6) # 6 = xlCSV
            
            Write-Host "  ✓ 已保存: $csvFileName" -ForegroundColor Green
            
        } catch {
            Write-Host "  ✗ 处理sheet '$sheetName' 时出错: $($_.Exception.Message)" -ForegroundColor Red
        }
    }
    
    Write-Host "`n转换完成！" -ForegroundColor Green
    Write-Host "所有CSV文件已保存到当前目录" -ForegroundColor Green
    
} catch {
    Write-Host "处理Excel文件时出错: $($_.Exception.Message)" -ForegroundColor Red
} finally {
    # 清理资源
    if ($workbook) {
        $workbook.Close($false)
    }
    if ($excel) {
        $excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    }
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

Write-Host "`n按任意键退出..."
$null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
